package de.herzog.bbp.files;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import de.herzog.bbp.model.Halle;
import de.herzog.bbp.model.Klasse;
import de.herzog.bbp.model.Mannschaft;
import de.herzog.bbp.model.Spiel;
import de.herzog.bbp.model.Verein;
import de.herzog.bbp.model.dao.AbstractDAO;
import de.herzog.bbp.model.dao.SpielDAO;
import de.herzog.bbp.model.dao.VereinDAO;

public class ImportExcel {

	private static final Logger LOG = LoggerFactory.getLogger(ImportExcel.class);

	private static boolean importedSpiele = false;
	private static boolean importedVereine = false;

	public static void importAll() throws FileNotFoundException, IOException {
		importAll(null);
	}

	public static void importAll(String fileName) throws FileNotFoundException, IOException {
		String vereineFileName = fileName.replace("Spiele", "Vereine");
		importVereine(vereineFileName);
		importSpiele(fileName);
	}

	public static synchronized void importSpiele(String fileName) throws FileNotFoundException, IOException {
		if (importedSpiele) {
			return;
		}
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileName));
		HSSFSheet sheet = workbook.getSheetAt(0);
		int first = sheet.getFirstRowNum() + 1;
		int last = sheet.getLastRowNum();
		LOG.info("Starting import of " + (last - first + 1) + " rows");

		for (int r = first; r <= last; r++) {
			importSpieleRow(sheet.getRow(r));
		}

		LOG.info("Klassen:  " + AbstractDAO.count(Klasse.class));
		LOG.info("Vereine:  " + AbstractDAO.count(Verein.class));
		LOG.info("Hallen:   " + AbstractDAO.count(Halle.class));
		LOG.info("Manschft: " + AbstractDAO.count(Mannschaft.class));
		LOG.info("Spiele:   " + AbstractDAO.count(Spiel.class));
		LOG.info("End import.");
		importedSpiele = true;
	}

	private static void importSpieleRow(Row row) {
		try {
			int col = 0;
			@SuppressWarnings("unused")
			String wochenTagValue = getStringCellValue(row, col++);
			Date datumValue = getDateCellValue(row, col++);
			String zeitValue = getStringCellValue(row, col++);
			String klasseValue = getStringCellValue(row, col++);
			String nummerValue = getStringCellValue(row, col++);
			String heimvereinValue = getStringCellValue(row, col++);
			String heimnummerValue = getStringCellValue(row, col++);
			String gastvereinValue = getStringCellValue(row, col++);
			String gastnummerValue = getStringCellValue(row, col++);
			String halleValue = getStringCellValue(row, col++);
			String schiriValue = getStringCellValue(row, col++);

			SpielDAO.insert(klasseValue, nummerValue, datumValue, zeitValue, heimvereinValue, heimnummerValue,
					gastvereinValue, gastnummerValue, heimvereinValue + halleValue, schiriValue);
		} catch (Throwable t) {
			LOG.warn("Excel-Zeile Nummer " + row.getRowNum() + " konnte nicht importiert werden: " + t);
		}
	}

	public static synchronized void importVereine(String fileName) throws FileNotFoundException, IOException {
		if (importedVereine) {
			return;
		}
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileName));
		HSSFSheet sheet = workbook.getSheetAt(0);
		int first = sheet.getFirstRowNum() + 1;
		int last = sheet.getLastRowNum();
		LOG.info("Starting import of " + (last - first + 1) + " rows");

		for (int r = first; r <= last; r++) {
			importVereineRow(sheet.getRow(r));
		}

		LOG.info("End import.");
		LOG.info("Vereine:  " + AbstractDAO.count(Verein.class));
		importedVereine = true;
	}

	private static void importVereineRow(Row row) {
		int col = 0;
		String nameValue = getStringCellValue(row, col++);
		String langNameValue = getStringCellValue(row, col++);
		String nummerValue = getStringCellValue(row, col++);

		VereinDAO.insert(nameValue, langNameValue, nummerValue);
	}

	private static String getStringCellValue(Row row, int col) {
		String value = null;
		Cell cell = row.getCell(col);
		if (cell == null) {
			value = null;
		} else {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				value = new HSSFDataFormatter().formatCellValue(cell);
				break;
			default:
				value = null;
			}
		}
		return value;
	}

	private static Date getDateCellValue(Row row, int col) {
		Date value = null;
		Cell cell = row.getCell(col);
		if (cell == null) {
			value = null;
		} else {
			value = cell.getDateCellValue();
		}
		return value;
	}

	public static void main(String[] args) throws FileNotFoundException, IOException {
		String fileName = args.length > 0 ? args[0] : null;
		importAll(fileName);
	}

}
